Stock valuation

It's important that you know the value of your stock as you need this for financial reports. The stock valuation report shows the quantity and value of each of your stock items.

Run the stock valuation report

Open: Stock Control > Reports > Status > Valuation, or select the items from the Stock list a choose Reports > Valuation

  • For a current valuation, enter today's date as the Valuation Date.

How stock quantities are calculated

The quantity that your have in stock for any stock items are updated in Sage 200 when:

  • A purchase order for the item is recorded as received in Sage 200.
  • A return for the item has been recorded as despatched in Sage 200.
  • The stock item has been added using the Add Stock option.
  • Stock is issued to (or returned from) an internal area or supplier for repair.
  • Stock is issued to a customer free of charge.
  • A difference has been found as part of a stock take and the stock level has been updated.

How stock values are calculated

The value of your stock is calculated using the cost price of the item, i.e. the buying price. So the value is the quantity multiplied by the cost price. The cost price is calculated differently depending on the Costing Method used for each stock item.

FIFO (First in First Out)

For this method, the stock is valued using the oldest cost price for the item until all the stock bought at that at that price is used, then the next cost price is used. This is calculated per warehouse or per bin.

The price is updated when a purchase order for item is recorded as received, or items are added using the Add stock screen.

You have a stock item that's stored in two different warehouses, Workshop and Showroom.

  1. You buy 5 items at £10 per item and receive them into the Workshop warehouse.

  2. You buy another 5 of the same item, this time for £15 per item and receive them into the Showroom warehouse.

    Your stock valuation is calculated as follows:

    Warehouse Stock level Cost price Stock value
    Workshop 5 10 50
    Showroom 5 15 75
    Total 10   125

  3. You sell an item from the Showroom warehouse.

    Your stock valuation is calculated as follows:

    Warehouse Stock level Cost price Stock value
    Workshop 5 10 50
    Showroom 4 15 60
    Total 9   120

  4. You buy a further 6 items at £11 per item and receive them into the Workshop warehouse.

    Your stock valuation is calculated as follows:

    Warehouse Stock level Cost price Stock value
    Workshop 5 10 50
      6 11 66
    Showroom 4 15 60
    Total 9   186

  5. You sell 7 items from the Workshop warehouse. As you bought the 5 at £10 first, these are sold first leaving you with 4 bought for £11 in the warehouse.

    Your stock valuation is calculated as follows:

    Warehouse Stock level Cost price Stock value
    Workshop 4 11 44
    Showroom 4 15 60
    Total 9   104

Average

For this method, the stock is valued using the average price paid for the item currently in stock. This is calculated from the total price paid for the items in stock divided by the number of goods in stock.

It's calculated in the following way:

((Current Total Qty in Stock * Current Average Buying Price) + (Quantity Brought In * Price Brought In))/ (Current Total Qty in Stock + Quantity Brought In).

The price is updated when a purchase order for item is recorded as received or items are added using the Add stock screen. It is not updated when goods are sold.

  1. You buy and receive 5 items at £10 each.

    Your stock valuation is as follows

    Stock level Average Buying price Stock value
    5 10 50

  2. You buy and receive a further 5 items at £15. The new average buying price is calculated as £12.50.

    Qty in Stock x Current Average buying price + Qty bought x cost price / Current Qty + New Qty
    50 (5 x 10) + 75 (5 x 15) / 10 (5 + 5)

    Your stock valuation is as follows

    Stock level Average Buying price Stock value
    10 12.5 125

  3. You sell an item. As this doesn't affect the buying price, the average buying price is still £12.50.

    Your stock valuation is as follows

    Stock level Average Buying price Stock value
    9 12.5 112.5

  4. You but another 6 items at £11. The new average buying price is calculated at £11.90

    Qty in Stock x Current Average buying price + Qty bought x cost price / Current Qty + New Qty
    112.5 (9 x 12.50) + 66 (6 x 11) / 15 (9 + 6)

    Your stock valuation is as follows:

    Stock level Average Buying price Stock value
    15 11.9 178.5

Standard

For this method, the cost price stated on the stock record is used. The price is only updated when the cost price is changed on the stock item.

  1. The standard cost price of the item is set at £10.

  2. You buy 5 items at £10 each.

    Your stock valuation is as follows:

    Stock level Standard cost price Stock value
    5 10 50

  3. You buy a further 5 items at £15 each. The standard cost price remains the same.

    Your stock valuation is as follows:

    Stock level Standard cost price Stock value
    10 10 100

  4. The standard cost price on the item record is changed to £11.

    Your stock valuation is as follows:

    Stock level Standard cost price Stock value
    10 11 110

 

Run a retrospective stock valuation report

You can calculate the cost value of your products at a particular date in the past.

Open: Stock Control > Reports > Status > Valuation, or select the items from the Stock list a choose Reports > Valuation

  • For a retrospective report, enter the retrospective date as the Valuation Date.

In the following examples, the following stock item and stock transactions are used to explain how the stock is valued retrospectively.

  • You have a stock level of 10 items on 30/07 and the value is £100.
  • You run a retrospective stock valuation report for the 10/07.

  • The following stock transactions are dated between the 10/07 and 30/07.

    Date Transaction Type Quantity Cost price Value
    15/07 POP order received 2 8 16
    13/07 SOP order despatched 5 12 60

How the retrospective quantity is calculated

The retrospective quantity is calculated by reversing the transactions entered for the stock item from the current date back to the specified valuation date.

This shows how the stock quantity is calculated on 30/07 for a retrospective stock valuation report with a valuation date of 10/07 for the following transactions.

Date Transaction Type Stock Movement Retrospective calculation Stock Level
30/07       10
15/07 POP Order received 2 -2 8
13/07 SOP Order despatched 5 +5 13

How the retrospective value is calculated

The retrospective stock value is calculated differently depending on the Costing Method used for each stock item.

  • FIFO

    This is calculated by taking the starting value of the stock and reversing the transactions entered for the stock item since valuation date.

    This shows how the retrospective value is calculated for the on 30/07 for a retrospective stock valuation report with a valuation date of 10/07.

    Date Transaction Type Stock Movement Cost price Retrospective calculation quantity Retrospective calculation value Stock Level Stock value
    30/07           10 100

    15/07

    POP order received

    2

    		 8

    -2

    		 -16 8

    84

    (100 - 16)

    13/07

    SOP order despatched

    5

    		 12

    +5

    		 + 60 13

    144

    (84 + 60)

    So the stock value is £144

  • Average

    (Retrospective Quantity) x (Average buying price of the item on the Valuation date specified for the report).

    Each time a purchase invoice or an addition of stock is recorded for an item, the average buying price is updated and recorded by Sage 200 using the current system date on your PC.

    The Average buying price is displayed on the report as the Valuation price.

    For the example item above, the average buying price on 10/07 was £10. On 15/07 this was changed when 2 new items were received for £11.

    When running the report with a valuation date of 10/07, the retrospective value is the retrospective quantity (13) x the average buying price on the 10/07 (£10). The stock valuation is £130.

    Date Transaction Type Stock Movement Cost price Retrospective calculation quantity Retrospective calculation value Stock Level Stock value
    30/07           10  

    15/07

    POP order received

    2

    		 8

    -2

    		 -16 8  

    13/07

    SOP order despatched

    5

    		 12

    +5

    		 + 60 13 13 x 10

  • Standard

    (Quantity) x (Standard cost price of the item on the Valuation date specified for the report)

    The standard cost price is displayed on the report as the Valuation price.

    Each time the standard cost price of the item is changed on the stock record, the new price and the date of the change are recorded by Sage 200 using the current date on your PC.

    For this example, the standard cost of the item on 10/07 was £8. This was changed on 15/07 to £10. When running the report with a valuation date of 10/07, the retrospective value is the retrospective quantity x the standard cost on the 10/07.

    Date Transaction Type Stock Movement Cost price Retrospective calculation quantity Retrospective calculation value Stock Level Stock value
    30/07           10  

    15/07

    POP order received

    2

    		 8

    -2

    		 -16 8  

    13/07

    SOP order despatched

    5

    		 12

    +5

    		 + 60 13 13 x 8

Common questions

Why are some items not shown on the report?

This can be due to the following:

  • The stock item has stock level of zero for the date specified on the report. Stock items with a level of zero are excluded from the report.

  • The stock item has no cost or transaction history for the valuation date specified for the report.

    If this is the case the following message is printed at the end of the report: Stock items will be excluded from this report if no cost or transaction history is available for the selected date. This occurs if you have backdated your transactions.

    Sage 200 uses the current system date to record changes made to the standard and average cost prices of stock items. If you run a retrospective stock valuation report with valuation date prior to the current system date, Sage 200 does not have any record of the cost price for the stock item. In the following example the current system date is 30/07.

    • 30/07 - You create a new stock item.
    • 30/07 - You enter a purchase order for the item at dated 01/07.
    • 30/07 - You record the receipt of the goods dated 05/07.
    • 30/07 - You run a current valuation report (As at 30/07). This shows the quantity and value of the stock.
    • 30/07 - You run a retrospective stock valuation) report with a valuation date of 15/07. The stock item does not appear on the report. This is because the cost price of the item was recorded on the 30/07 and Sage 200 has no record of a cost price prior to this.

If I run a current valuation report and then run it retrospectively for the same date a few days later, why are the values different?

This can be due to the following:

  • If you have revalued your stock, for stock items using the FIFO costing method.

  • If the standard cost price on a stock item is changed more than once in the same day, and the current valuation report was produced before the final price change.

Can I modify the report to use different criteria?

We recommend that you do not modify the Stock Valuation report, to include in and between on the Warehouse criteria. Doing so may produce incorrect figures, so you should only use is with the Warehouse criteria.

We also advise not to create a new report that includes these criteria.

See also

Related information

Costing methods

Stock locations (warehouses)